package com.tbynet.jwp.repository.impl;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.tbynet.jwp.framework.aop.Aop;
import com.tbynet.jwp.framework.core._JwpServiceProvider;
import com.tbynet.jwp.framework.kit.StringKit;
import com.tbynet.jwp.model.Posts;
import com.tbynet.jwp.repository.spi.PostSearchService;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 文章搜索服务实现类
 * Service层职责：单一的搜索相关数据操作，包含SQL代码
 *
 * @author 佰亿互联
 * @version 2.0
 * @since 2025-11-04
 */
@Aop(PostSearchService.class)
public class PostSearchServiceImpl extends _JwpServiceProvider<Posts> implements PostSearchService {

    /** 默认搜索建议数量 */
    private static final int DEFAULT_SUGGESTION_LIMIT = 10;

    /** 默认相似文章数量 */
    private static final int DEFAULT_SIMILAR_POSTS_LIMIT = 5;

    /** 默认文章类型 */
    private static final String DEFAULT_POST_TYPE = "post";

    /** 默认发布状态 */
    private static final String DEFAULT_PUBLISH_STATUS = "publish";

    @Override
    protected String buildSearchWhereSql(String keyword, List<Object> params) {
        return "";
    }
    
    /**
     * 搜索文章
     * Service层职责：单一的组合条件搜索操作
     *
     * @param pageNumber 页码
     * @param pageSize 每页大小
     * @param type 文章类型
     * @param status 文章状态
     * @param q 搜索关键词
     * @return 分页结果
     */
    @Override
    public Page<Posts> search(int pageNumber, int pageSize, String type, String status, String q) {
        log.debug("搜索文章，页码: %s, 大小: %s, 类型: %s, 状态: %s, 关键词: %s",
                pageNumber, pageSize, type, status, q);

        List<Object> paras = new ArrayList<Object>();
        StringBuilder sql = new StringBuilder("from wp_posts where 1=1");

        // 构建查询条件
        buildSearchConditions(sql, paras, type, status, q);

        // 添加排序
        sql.append(" order by post_modified desc ");

        return getDao().paginate(pageNumber, pageSize, "select *", sql.toString(), paras.toArray());
    }

    /**
     * 全文搜索
     * Service层职责：单一的全文搜索操作
     *
     * @param keyword 关键词
     * @param page 页码
     * @param size 每页大小
     * @return 分页结果
     */
    @Override
    public Page<Posts> fullTextSearch(String keyword, int page, int size) {
        log.debug("全文搜索，关键词: %s, 页码: %s, 大小: %s", keyword, page, size);

        if (StrKit.isBlank(keyword)) {
            return new Page<>(new ArrayList<>(), page, size, 0, 0);
        }

        List<Object> params = new ArrayList<>();
        StringBuilder sql = new StringBuilder("FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' ");
        sql.append("AND (post_title LIKE ? OR post_content LIKE ? OR post_excerpt LIKE ?) ");
        sql.append("ORDER BY post_date DESC");

        String likeKeyword = "%" + keyword + "%";
        params.add(likeKeyword);
        params.add(likeKeyword);
        params.add(likeKeyword);

        return getDao().paginate(page, size, "SELECT *", sql.toString(), params.toArray());
    }

    /**
     * 高级搜索
     * Service层职责：单一的多条件组合搜索操作
     *
     * @param conditions 搜索条件
     * @param page 页码
     * @param size 每页大小
     * @return 分页结果
     */
    @Override
    public Page<Posts> advancedSearch(Map<String, String> conditions, int page, int size) {
        log.debug("高级搜索，条件数量: %s, 页码: %s, 大小: %s",
                conditions != null ? conditions.size() : 0, page, size);

        List<Object> params = new ArrayList<>();
        StringBuilder sql = new StringBuilder("FROM wp_posts p WHERE 1=1 ");

        // 文章类型
        sql.append("AND p.post_type = 'post' ");

        // 转换条件类型并构建搜索条件
        Map<String, Object> objectConditions = convertConditionsToObject(conditions);
        buildAdvancedSearchConditions(sql, params, objectConditions);

        sql.append("ORDER BY p.post_date DESC");

        return getDao().paginate(page, size, "SELECT p.*", sql.toString(), params.toArray());
    }

    /**
     * 高级搜索文章
     */
    @Override
    public Page<Posts> advancedSearch(int pageNumber, int pageSize, String type, String status,
                                      String keyword, String category, String tag,
                                      String month, Boolean sticky) {
        try {
            // 构建SQL和参数
            StringBuilder sqlBuilder = new StringBuilder("FROM wp_posts p WHERE 1=1");
            List<Object> params = new ArrayList<>();

            // 文章类型
            if (StrKit.notBlank(type)) {
                sqlBuilder.append(" AND p.post_type = ?");
                params.add(type);
            } else {
                sqlBuilder.append(" AND p.post_type = 'post'");
            }

            // 文章状态
            if (StrKit.notBlank(status)) {
                sqlBuilder.append(" AND p.post_status = ?");
                params.add(status);
            } else {
                // 默认排除回收站和自动草稿
                sqlBuilder.append(" AND p.post_status != 'trash' AND p.post_status != 'auto-draft'");
            }

            // 关键词搜索（标题和内容）
            if (StrKit.notBlank(keyword)) {
                sqlBuilder.append(" AND (p.post_title LIKE ? OR p.post_content LIKE ?)");
                String likeKeyword = "%" + keyword + "%";
                params.add(likeKeyword);
                params.add(likeKeyword);
            }

            // 月份筛选
            if (StrKit.notBlank(month) && month.length() == 6) {
                String year = month.substring(0, 4);
                String monthNum = month.substring(4, 6);
                sqlBuilder.append(" AND YEAR(p.post_date) = ? AND MONTH(p.post_date) = ?");
                params.add(year);
                params.add(monthNum);
            }

            // 分类筛选
            if (StrKit.notBlank(category)) {
                sqlBuilder.append(" AND EXISTS (SELECT 1 FROM wp_term_relationships tr ");
                sqlBuilder.append(" JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id ");
                sqlBuilder.append(" WHERE tr.object_id = p.ID AND tt.taxonomy = 'category' ");

                // 处理分类ID或分类slug
                if (StringKit.isNumeric(category)) {
                    sqlBuilder.append(" AND tt.term_taxonomy_id = ?)");
                    params.add(Integer.parseInt(category));
                } else {
                    sqlBuilder.append(" AND tt.term_id IN (SELECT term_id FROM wp_terms WHERE slug = ?))");
                    params.add(category);
                }
            }

            // 标签筛选
            if (StrKit.notBlank(tag)) {
                sqlBuilder.append(" AND EXISTS (SELECT 1 FROM wp_term_relationships tr ");
                sqlBuilder.append(" JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id ");
                sqlBuilder.append(" WHERE tr.object_id = p.ID AND tt.taxonomy = 'post_tag' ");

                // 处理标签ID或标签slug
                if (StringKit.isNumeric(tag)) {
                    sqlBuilder.append(" AND tt.term_taxonomy_id = ?)");
                    params.add(Integer.parseInt(tag));
                } else {
                    sqlBuilder.append(" AND tt.term_id IN (SELECT term_id FROM wp_terms WHERE slug = ?))");
                    params.add(tag);
                }
            }

            // 置顶文章筛选
            if (sticky != null) {
                if (sticky) {
                    // 获取置顶文章ID列表
                    List<Integer> stickyPostIds = getStickyPostIds();
                    if (!stickyPostIds.isEmpty()) {
                        sqlBuilder.append(" AND p.ID IN (");
                        for (int i = 0; i < stickyPostIds.size(); i++) {
                            if (i > 0) sqlBuilder.append(",");
                            sqlBuilder.append("?");
                            params.add(stickyPostIds.get(i));
                        }
                        sqlBuilder.append(")");
                    } else {
                        // 如果没有置顶文章，返回空结果
                        sqlBuilder.append(" AND 1=0");
                    }
                } else {
                    // 获取置顶文章ID列表并排除
                    List<Integer> stickyPostIds = getStickyPostIds();
                    if (!stickyPostIds.isEmpty()) {
                        sqlBuilder.append(" AND p.ID NOT IN (");
                        for (int i = 0; i < stickyPostIds.size(); i++) {
                            if (i > 0) sqlBuilder.append(",");
                            sqlBuilder.append("?");
                            params.add(stickyPostIds.get(i));
                        }
                        sqlBuilder.append(")");
                    }
                }
            }

            // 排序
            sqlBuilder.append(" ORDER BY p.post_date DESC");

            // 构建完整的SQL
            String selectSql = "SELECT p.* " + sqlBuilder.toString();
            String countSql = "SELECT COUNT(*) " + sqlBuilder.toString();

            // 执行查询
            List<Posts> postList = getDao().find(selectSql, params.toArray());
            Long totalRow = Db.queryLong(countSql, params.toArray());

            // 创建分页对象
            int totalPage = (int) Math.ceil((double) totalRow / pageSize);
            return new Page<>(postList, pageNumber, pageSize, totalPage, totalRow.intValue());

        } catch (Exception e) {
            log.error("高级搜索文章异常", e);
            return new Page<>(new ArrayList<>(), pageNumber, pageSize, 0, 0);
        }
    }

    /**
     * 获取搜索建议
     * Service层职责：单一的搜索建议查询操作
     *
     * @param query 查询词
     * @return 建议列表
     */
    @Override
    public List<String> getSearchSuggestions(String query) {
        log.debug("获取搜索建议，查询词: %s", query);

        if (StrKit.isBlank(query) || query.length() < 2) {
            return new ArrayList<>();
        }

        String sql = "SELECT DISTINCT post_title FROM wp_posts " +
                "WHERE post_type = 'post' AND post_status = 'publish' " +
                "AND post_title LIKE ? " +
                "ORDER BY post_date DESC " +
                "LIMIT ?";

        List<Record> records = Db.find(sql, "%" + query + "%", DEFAULT_SUGGESTION_LIMIT);
        List<String> suggestions = new ArrayList<>();

        for (Record record : records) {
            suggestions.add(record.getStr("post_title"));
        }

        log.debug("获取到搜索建议数量: %s", suggestions.size());
        return suggestions;
    }

    /**
     * 获取热门搜索关键词
     * Service层职责：单一的热门关键词查询操作
     *
     * @return 热门关键词列表
     */
    @Override
    public List<String> getHotSearchKeywords() {
        log.debug("获取热门搜索关键词");

        // 这里可以从搜索日志表中获取热门关键词
        // 暂时返回固定关键词
        List<String> hotKeywords = new ArrayList<>();
        hotKeywords.add("Java");
        hotKeywords.add("WordPress");
        hotKeywords.add("开发");
        hotKeywords.add("编程");
        hotKeywords.add("技术");

        log.debug("返回热门关键词数量: %s", hotKeywords.size());
        return hotKeywords;
    }

    /**
     * 重建搜索索引
     * Service层职责：单一的索引重建操作
     *
     * @return 重建结果
     */
    @Override
    public boolean rebuildSearchIndex() {
        log.info("开始重建搜索索引");

        // 重建搜索索引的逻辑
        // 这里可以集成Lucene、Elasticsearch等搜索引擎
        // 暂时返回true表示成功

        log.info("重建搜索索引完成");
        return true;
    }

    /**
     * 查找相似文章
     * Service层职责：单一的相似文章查询操作
     *
     * @param postId 文章ID
     * @param limit 限制数量
     * @return 相似文章列表
     */
    @Override
    public List<Posts> findSimilarPosts(Object postId, int limit) {
        log.debug("查找相似文章，文章ID: %s, 限制数量: %s", postId, limit);

        Posts post = getDao().findById(postId);
        if (post == null) {
            log.warn("查找相似文章失败，文章不存在，文章ID: %s", postId);
            return new ArrayList<>();
        }

        // 基于标题和内容查找相似文章
        String sql = "SELECT *, " +
                "MATCH(post_title, post_content) AGAINST(? IN NATURAL LANGUAGE MODE) as relevance " +
                "FROM wp_posts " +
                "WHERE post_type = 'post' AND post_status = 'publish' AND ID != ? " +
                "ORDER BY relevance DESC " +
                "LIMIT ?";

        String searchText = post.getPostTitle() + " " + post.getPostContent();
        int actualLimit = limit > 0 ? limit : DEFAULT_SIMILAR_POSTS_LIMIT;

        List<Posts> similarPosts = getDao().find(sql, searchText, postId, actualLimit);
        log.debug("找到相似文章数量: %s", similarPosts.size());

        return similarPosts;
    }

    /**
     * 按分类搜索
     * Service层职责：单一的分类搜索操作
     *
     * @param category 分类
     * @param page 页码
     * @param size 每页大小
     * @return 分页结果
     */
    @Override
    public Page<Posts> searchByCategory(String category, int page, int size) {
        log.debug("按分类搜索，分类: %s, 页码: %s, 大小: %s", category, page, size);

        String sql = "FROM wp_posts p " +
                "INNER JOIN wp_term_relationships tr ON p.ID = tr.object_id " +
                "INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id " +
                "WHERE p.post_type = 'post' AND p.post_status = 'publish' " +
                "AND tt.taxonomy = 'category' AND tt.term_id = ? " +
                "ORDER BY p.post_date DESC";

        return getDao().paginate(page, size, "SELECT p.*", sql, category);
    }

    /**
     * 按标签搜索
     * Service层职责：单一的标签搜索操作
     *
     * @param tag 标签
     * @param page 页码
     * @param size 每页大小
     * @return 分页结果
     */
    @Override
    public Page<Posts> searchByTag(String tag, int page, int size) {
        log.debug("按标签搜索，标签: %s, 页码: %s, 大小: %s", tag, page, size);

        String sql = "FROM wp_posts p " +
                "INNER JOIN wp_term_relationships tr ON p.ID = tr.object_id " +
                "INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id " +
                "WHERE p.post_type = 'post' AND p.post_status = 'publish' " +
                "AND tt.taxonomy = 'post_tag' AND tt.term_id = ? " +
                "ORDER BY p.post_date DESC";

        return getDao().paginate(page, size, "SELECT p.*", sql, tag);
    }

    /**
     * 按作者搜索
     * Service层职责：单一的作者搜索操作
     *
     * @param author 作者
     * @param page 页码
     * @param size 每页大小
     * @return 分页结果
     */
    @Override
    public Page<Posts> searchByAuthor(String author, int page, int size) {
        log.debug("按作者搜索，作者: %s, 页码: %s, 大小: %s", author, page, size);

        String sql = "FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' " +
                "AND post_author = ? ORDER BY post_date DESC";

        return getDao().paginate(page, size, "SELECT *", sql, author);
    }

    /**
     * 按日期范围搜索
     * Service层职责：单一的日期范围搜索操作
     *
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @param page 页码
     * @param size 每页大小
     * @return 分页结果
     */
    @Override
    public Page<Posts> searchByDateRange(String startDate, String endDate, int page, int size) {
        log.debug("按日期范围搜索，开始日期: %s, 结束日期: %s, 页码: %s, 大小: %s",
                startDate, endDate, page, size);

        List<Object> params = new ArrayList<>();
        StringBuilder sql = new StringBuilder("FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' ");

        if (StrKit.notBlank(startDate)) {
            sql.append("AND post_date >= ? ");
            params.add(startDate);
        }

        if (StrKit.notBlank(endDate)) {
            sql.append("AND post_date <= ? ");
            params.add(endDate);
        }

        sql.append("ORDER BY post_date DESC");

        return getDao().paginate(page, size, "SELECT *", sql.toString(), params.toArray());
    }

    // ============ 重写父类方法 ============

    /**
     * 构建搜索条件
     * 添加文章特定的搜索条件
     *
     * @param sqlBuilder SQL构建器
     * @param params 参数列表
     * @param keyword 搜索关键词
     */
    protected void buildSearchConditions(StringBuilder sqlBuilder, List<Object> params, String keyword) {
        if (StrKit.notBlank(keyword)) {
            // 文章特定的搜索逻辑：在标题、内容和摘要中搜索
            sqlBuilder.append(" AND (post_title LIKE ? OR post_content LIKE ? OR post_excerpt LIKE ?)");
            String likeKeyword = "%" + keyword + "%";
            params.add(likeKeyword);
            params.add(likeKeyword);
            params.add(likeKeyword);
        }
    }

    /**
     * 构建高级搜索条件
     * 添加文章特定的高级搜索条件
     *
     * @param sqlBuilder SQL构建器
     * @param params 参数列表
     * @param conditions 搜索条件
     */
    protected void buildAdvancedSearchConditions(StringBuilder sqlBuilder, List<Object> params, Map<String, Object> conditions) {
        // 添加文章特定的高级搜索条件
        buildPostAdvancedSearchConditions(sqlBuilder, params, conditions);
    }

    // ============ 文章特定的私有方法 ============
    /**
     * 获取置顶文章ID列表
     */
    private List<Integer> getStickyPostIds() {
        try {
            // 这里需要根据您的置顶文章存储方式来实现
            // 假设置顶文章ID存储在 wp_options 表中
            List<Record> records = Db.find("SELECT option_value FROM wp_options WHERE option_name = 'sticky_posts'");
            if (records != null && !records.isEmpty()) {
                String stickyPostsValue = records.get(0).getStr("option_value");
                // 解析置顶文章ID列表（假设是以逗号分隔的字符串）
                if (StrKit.notBlank(stickyPostsValue)) {
                    List<Integer> stickyPostIds = new ArrayList<>();
                    String[] ids = stickyPostsValue.split(",");
                    for (String id : ids) {
                        if (StringKit.isNumeric(id)) {
                            stickyPostIds.add(Integer.parseInt(id));
                        }
                    }
                    return stickyPostIds;
                }
            }
        } catch (Exception e) {
            log.error("获取置顶文章ID列表异常", e);
        }
        return new ArrayList<>();
    }

    /**
     * 构建文章特定的高级搜索条件
     * Service层私有方法：构建文章特有的高级搜索SQL条件
     *
     * @param sql SQL构建器
     * @param params 参数列表
     * @param conditions 搜索条件
     */
    private void buildPostAdvancedSearchConditions(StringBuilder sql, List<Object> params,
                                                   Map<String, Object> conditions) {
        if (conditions == null) {
            return;
        }

        // 分类筛选
        Object category = conditions.get("category");
        if (category != null && StrKit.notBlank(category.toString())) {
            sql.append("AND EXISTS (SELECT 1 FROM wp_term_relationships tr ");
            sql.append("INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id ");
            sql.append("WHERE tr.object_id = p.ID AND tt.taxonomy = 'category' AND tt.term_id = ?) ");
            params.add(category);
        }

        // 标签筛选
        Object tag = conditions.get("tag");
        if (tag != null && StrKit.notBlank(tag.toString())) {
            sql.append("AND EXISTS (SELECT 1 FROM wp_term_relationships tr ");
            sql.append("INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id ");
            sql.append("WHERE tr.object_id = p.ID AND tt.taxonomy = 'post_tag' AND tt.term_id = ?) ");
            params.add(tag);
        }

        // 文章格式筛选
        Object format = conditions.get("format");
        if (format != null && StrKit.notBlank(format.toString())) {
            sql.append("AND EXISTS (SELECT 1 FROM wp_term_relationships tr ");
            sql.append("INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id ");
            sql.append("WHERE tr.object_id = p.ID AND tt.taxonomy = 'post_format' AND tt.term_id = ?) ");
            params.add(format);
        }

        // 评论状态筛选
        Object commentStatus = conditions.get("comment_status");
        if (commentStatus != null && StrKit.notBlank(commentStatus.toString())) {
            sql.append("AND p.comment_status = ? ");
            params.add(commentStatus);
        }

        // Ping状态筛选
        Object pingStatus = conditions.get("ping_status");
        if (pingStatus != null && StrKit.notBlank(pingStatus.toString())) {
            sql.append("AND p.ping_status = ? ");
            params.add(pingStatus);
        }

        // 作者筛选
        Object author = conditions.get("author");
        if (author != null && StrKit.notBlank(author.toString())) {
            sql.append("AND p.post_author = ? ");
            params.add(author);
        }
    }

    /**
     * 构建文章搜索条件
     * Service层私有方法：构建基础文章搜索SQL条件
     *
     * @param sql SQL构建器
     * @param params 参数列表
     * @param type 文章类型
     * @param status 文章状态
     * @param q 搜索关键词
     */
    private void buildSearchConditions(StringBuilder sql, List<Object> params,
                                       String type, String status, String q) {
        if(StrKit.notBlank(type)) {
            sql.append(" and post_type=? ");
            params.add(type);
        }

        if(StrKit.notBlank(status)) {
            sql.append(" and post_status=? ");
            params.add(status);
        } else {
            sql.append(" and post_status!=? ");
            params.add(Posts.POST_STATUS_TRASH);
        }

        // 使用重写的搜索条件构建方法
        buildSearchConditions(sql, params, q);
    }

    /**
     * 转换条件类型
     * Service层私有方法：将Map<String, String>转换为Map<String, Object>
     *
     * @param stringConditions 字符串条件
     * @return 对象条件
     */
    private Map<String, Object> convertConditionsToObject(Map<String, String> stringConditions) {
        if (stringConditions == null) {
            return new HashMap<>();
        }

        Map<String, Object> objectConditions = new HashMap<>();
        objectConditions.putAll(stringConditions);
        return objectConditions;
    }

    /**
     * 验证搜索参数
     * Service层私有方法：参数验证
     *
     * @param page 页码
     * @param size 每页大小
     * @return 是否有效
     */
    private boolean validateSearchParams(int page, int size) {
        return page > 0 && size > 0 && size <= 100;
    }

    /**
     * 构建文章特定的排序条件
     * Service层私有方法：构建文章特有的排序SQL
     *
     * @param sql SQL构建器
     * @param orderBy 排序字段
     * @param order 排序方向
     */
    private void buildPostOrderBy(StringBuilder sql, String orderBy, String order) {
        if (StrKit.isBlank(orderBy)) {
            sql.append(" ORDER BY post_date DESC");
            return;
        }

        String direction = "ASC".equalsIgnoreCase(order) ? "ASC" : "DESC";

        switch (orderBy.toLowerCase()) {
            case "title":
                sql.append(" ORDER BY post_title ").append(direction);
                break;
            case "author":
                sql.append(" ORDER BY post_author ").append(direction);
                break;
            case "modified":
                sql.append(" ORDER BY post_modified ").append(direction);
                break;
            case "comment_count":
                sql.append(" ORDER BY comment_count ").append(direction);
                break;
            default:
                sql.append(" ORDER BY post_date DESC");
                break;
        }
    }
}